package com.hellogood.yaohuo.server.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.DBConnection;
import com.hellogood.yaohuo.server.db.DBHelper;
import com.hellogood.yaohuo.server.mgr.factory.MgrFactory;
import com.hellogood.yaohuo.shared.data.Constants;
import com.hellogood.yaohuo.shared.entity.Form;
import com.hellogood.yaohuo.shared.exception.DatabaseException;
import com.hellogood.yaohuo.shared.vo.MainFrame;

public class FormDAO extends DBHelper {
	Logger log = Logger.getLogger(this.getClass());

	@Override
	public List<?> initializeObject(ResultSet rs) throws SQLException {
		List<Form> formList = new ArrayList<Form>();
		if (rs != null) {
			boolean hasOrgName = false;
			boolean hasVendorCode = false;
			boolean hasVendorName = false;
			boolean hasShopCode = false;
			boolean hasShopName = false;
			boolean hasStatus = false;
			boolean hasCost = false;
			boolean hasTotal = false;
			boolean hasQtyh = false;
			boolean hasPsCost = false;
			int columns = rs.getMetaData().getColumnCount();
			for (int i = 1; i < columns + 1; i++) {
				String columnName = rs.getMetaData().getColumnName(i);
				if (columnName.equals("OrgName")) {
					hasOrgName = true;
				} else if (columnName.equals("VendorCode")) {
					hasVendorCode = true;
				} else if (columnName.equals("VendorName")) {
					hasVendorName = true;
				} else if (columnName.equals("ShopCode")) {
					hasShopCode = true;
				} else if (columnName.equals("ShopName")) {
					hasShopName = true;
				} else if (columnName.equals("Status")) {
					hasStatus = true;
				} else if (columnName.equals("Cost") || columnName.equals("cost")) {
					hasCost = true;
				} else if (columnName.equals("Total")) {
					hasTotal = true;
				} else if (columnName.equals("Qtyh")) {
					hasQtyh = true;
				} else if (columnName.equals("PsCost") || columnName.equals("pscost")) {
					hasPsCost = true;
				}
			}
			while (rs.next()) {
				Form form = new Form();
				form.setBillNo(rs.getString("billNo") == null ? "" : rs.getString("billNo").trim());
				form.setBillDate(rs.getString("billDate") == null ? "" : rs.getString("billDate").trim());
				form.setOrgCode(rs.getString("OrgCode") == null ? "" : rs.getString("OrgCode").trim());
				if (hasOrgName) {
					form.setOrgName(rs.getString("OrgName") == null ? "" : rs.getString("OrgName").trim());
				}
				if (hasShopCode) {
					form.setShopCode(rs.getString("ShopCode") == null ? "" : rs.getString("ShopCode").trim());
				}
				if (hasVendorCode) {
					form.setVendorCode(rs.getString("VendorCode") == null ? "" : rs.getString("VendorCode").trim());
				}
				if (hasVendorName) {
					form.setVendorName(rs.getString("VendorName") == null ? "" : rs.getString("VendorName").trim());
				}
				if (hasShopName) {
					form.setShopName(rs.getString("ShopName") == null ? "" : rs.getString("ShopName").trim());
				}
				if (hasStatus) {
					form.setStatus(rs.getString("status") == null ? "" : rs.getString("status").trim());
				}
				form.setCounts(rs.getInt("Counts"));
				if (hasCost) {
					form.setCost(rs.getBigDecimal("Cost"));
					form.setNetCost(rs.getBigDecimal("NetCost"));
				}
				if (hasTotal) {
					form.setTotal(rs.getBigDecimal("Total"));
				}
				form.setStaffCode(rs.getString("StaffCode") == null ? "" : rs.getString("StaffCode").trim());
				form.setStaffName(rs.getString("StaffName") == null ? "" : rs.getString("StaffName").trim());
				form.setTimeMark(rs.getString("TimeMark") == null ? "" : rs.getString("TimeMark").trim());
				form.setRzDate(rs.getString("RzDate") == null ? "" : rs.getString("RzDate").trim());
				form.setRemark(rs.getString("Remark") == null ? "" : rs.getString("Remark").trim());
				if (hasQtyh) {
					form.setZiyou(rs.getString("Qtyh") == null ? "" : rs.getString("Qtyh").trim());
				}
				if (hasPsCost) {
					form.setPsCost(rs.getBigDecimal("PsCost"));
				}
				formList.add(form);
			}
		}
		return formList;
	}

	public String getLastBillNo(String billNo, String tableFormName, String billNoField) {
		String sql = "SELECT TOP 1 " + billNoField + " AS billNo FROM " + tableFormName + " WHERE " + billNoField + " LIKE '" + billNo + "%' ORDER BY billNo DESC";
		return super.getLastBillNo(sql, "billNo");
	}

	@SuppressWarnings("unchecked")
	public List<Form> searchForm(String tableFormName, String billNoField, String dateField, String orgCode, String startDate, String endDate, String rz) {
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT (SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName,f.*,f." + billNoField + " AS billNo,f." + dateField + " AS billDate FROM "
				+ tableFormName + " f WHERE (f.OrgCode = '" + orgCode + "' OR f.VendorCode = '" + orgCode + "') AND cast(f." + dateField + " as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,'"
				+ startDate + "'),120) and cast(f." + dateField + " as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + endDate + "'),120)";
		if (rz.equals(Constants.RUZHANG_CODE_YES)) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals(Constants.RUZHANG_CODE_NO)) {
			sql = sql + " AND rzDate = ''";
		}
		List<?> list = super.query(sql);
		return (List<Form>) list;
	}

	@SuppressWarnings("unchecked")
	public List<Form> searchFormByQuyu(String tableFormName, String billNoField, String dateField, String quyuCode, String startDate, String endDate, String rz) {
		String hxDbName = DBConnection.hxDbName;
		String userCode = MainFrame.currentUser.getStaffCode();
		String orgs = "";
		if (quyuCode == null) {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUser(userCode);
		} else {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUserQuyu(userCode, quyuCode);
		}

		String sql = "SELECT (SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName,f.*,f." + billNoField + " AS billNo,f." + dateField + " AS billDate FROM "
				+ tableFormName + " f WHERE (f.OrgCode IN (" + orgs + ") OR f.VendorCode IN (" + orgs + ")) AND cast(f." + dateField + " as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,'"
				+ startDate + "'),120) and cast(f." + dateField + " as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + endDate + "'),120)";
		if (rz.equals(Constants.RUZHANG_CODE_YES)) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals(Constants.RUZHANG_CODE_NO)) {
			sql = sql + " AND rzDate = ''";
		}
		List<?> list = super.query(sql);
		return (List<Form>) list;
	}

	public Form getFormByBillNo(String tableFormName, String billNoField, String dateField, String billNo) {
		String hxDbName = DBConnection.hxDbName;
		String sql = "SELECT (SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName, f.*,f." + billNoField + " AS billNo,f." + dateField + " AS billDate FROM "
				+ tableFormName + " f WHERE f." + billNoField + " = '" + billNo + "'";
		List<?> list = super.query(sql);
		if (list != null && (!list.isEmpty())) {
			return (Form) list.get(0);
		}
		return null;
	}

	public String deleteForm(String tableFormName, String tableDetailName, String billNoField, String billNo) {
		String result = "";
		List<String> sqls = new ArrayList<String>();

		String sql = "DELETE FROM " + tableFormName + " WHERE " + billNoField + " = '" + billNo + "'";
		sqls.add(sql);
		sql = "DELETE FROM " + tableDetailName + " WHERE " + billNoField + " = '" + billNo + "'";
		sqls.add(sql);
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			System.out.println(sqls);
			log.error(sqls);
			log.error(e.getMessage());
			e.printStackTrace();
			return e.getMessage();
		}
		return result;
	}
}
